package com.iad.persistence;

import com.iad.entity.*;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.Query;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Order;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.apache.commons.lang.StringUtils;

/**
 * This class contain data access methods and properties
 * that utilize Spring's HibernateDAOSupport's HibernateTemplate.
 *
 * @author Nick Sophinos
 */

public class SiteDAO extends HibernateDaoSupport {
  

  public Site findUser(String pathLabel, String password) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Site.class);
    crit.add(Expression.eq("pathLabel", pathLabel));    
    crit.add(Expression.eq("password", password));    
    Site site = (Site)crit.uniqueResult();
    return site;
  }
  
  
  public Long deleteSong(Long id) throws Exception {
    Session session = getSession(); 
    System.out.println("deleting song id:" + id);
    HibernateTemplate template =  getHibernateTemplate();
    Song song = getSong(id);
    System.out.println("fetched song id:" + song.getId());
    template.delete(song);
    return id;
  }
  

  public Long newSite(Site site) throws Exception {
    Session session = getSession(); 
    HibernateTemplate template =  getHibernateTemplate();
    
    // Get the last id value for the insert
    System.out.println("template.save(" + site.getId() + ");");
    template.save(site);
    
    // create the site page entries
    
    Page bioPage = new Page();
    bioPage.setName("bio");
    bioPage.setTitle("Biography");
    bioPage.setContent("<content></content>");
    bioPage.setSiteId(site.getId());
    template.save(bioPage);
    
    Page latestPage = new Page();
    latestPage.setName("latest");
    latestPage.setTitle("The Latest");
    latestPage.setContent("<content></content>");
    latestPage.setSiteId(site.getId());
    template.save(latestPage);
    
    Playlist playlist = new Playlist(0L,site.getName(),site.getId(), null, null);
    template.save(playlist);
    
    return site.getId(); 
  }
  
  public Long newReview(Review review) throws Exception {
    HibernateTemplate template =  getHibernateTemplate();
    System.out.println("template.save(" + review.getId() + ");");
    template.save(review);
    return review.getId(); 
  }
  
  
  public Long newContactMessage(ContactMessage contactMessage) throws Exception {
    HibernateTemplate template =  getHibernateTemplate();
    
    // Get the last id value for the insert
    System.out.println("template.save(" + contactMessage.getId() + ");");
    template.save(contactMessage);
    return contactMessage.getId(); 
  }
  
  
  public Long newPlaylist(Playlist playlist) throws Exception {
    Session session = getSession(); 
    HibernateTemplate template =  getHibernateTemplate();
    
    // Get the last id value for the insert
    String queryString = "select max(p.id) from Playlist p";
    Query query = session.createQuery(queryString);
    Long lastId = (Long) query.uniqueResult();
    if (lastId == null) {
      lastId = new Long(0);
    }
    playlist.setId(++lastId);
    System.out.println("template.save(" + playlist.getId() + ");");
    template.save(playlist);
    return lastId; 
  }
  
  
  public Long newVideo(Video video) throws Exception {
    Session session = getSession(); 
    HibernateTemplate template =  getHibernateTemplate();
    
    // Get the last id value for the insert
    String queryString = "select max(v.id) from Video v";
    Query query = session.createQuery(queryString);
    Long lastId = (Long) query.uniqueResult();
    if (lastId == null) {
      lastId = new Long(0);
    }
    video.setId(++lastId);
    System.out.println("template.save(" + video.getId() + ");");
    template.save(video);
    return lastId; 
  }
  
  public void updateVideo(Long id, String fileName) throws Exception {
    Video video = getVideo(id); 
    video.setImagePath(fileName);
    HibernateTemplate template =  getHibernateTemplate();
    System.out.println("template.update(" + video.getId() + ");");
    template.update(video);
  }
  
  
  public void updatePlaylist(Long id, String fileName) throws Exception {
    Playlist playlist = getPlaylist(id); 
    playlist.setImagePath(fileName);
    HibernateTemplate template =  getHibernateTemplate();
    System.out.println("template.update(" + playlist.getId() + ");");
    template.update(playlist);
  }
  
  
  public void updatePlaylistDetails(Long siteId, String fileName, String playlistName) throws Exception {
    // fetch the only playlist associated with the site, since this is a new site. 
    Playlist playlist = getPlaylistFromNewSite(siteId); 
    playlist.setImagePath(fileName);
    playlist.setName(playlistName);
    HibernateTemplate template =  getHibernateTemplate();
    System.out.println("template.update(" + playlist.getId() + ");");
    template.update(playlist);
  }
  
  
  public void updateSite(Long id, String fileName) throws Exception {
    Site site = find(id); 
    site.setImagePath(fileName);
    HibernateTemplate template =  getHibernateTemplate();
    System.out.println("template.update(" + site.getId() + ");");
    template.update(site);
  }
  
  
  public void updateSiteProfile(Long id, Map data) throws Exception {
    Site site = find(id); 
    site.setName((String)data.get("bandName"));
    site.setBandCity((String)data.get("bandCity"));
    site.setBandState((String)data.get("bandState"));
    site.setEmail((String)data.get("email"));
    site.setPassword((String)data.get("password"));
    site.setFirstName((String)data.get("firstName"));
    site.setLastName((String)data.get("lastName"));
    site.setPathLabel((String)data.get("pathLabel"));
    site.setUrl((String)data.get("url"));
    HibernateTemplate template =  getHibernateTemplate();
    System.out.println("template.update(" + site.getId() + ");");
    template.update(site);
  }
  
  
  public void updateSiteImage(Long id, String fileName) throws Exception {
    Site site = find(id); 
    site.setImagePath(fileName);
    HibernateTemplate template =  getHibernateTemplate();
    System.out.println("template.update(" + site.getId() + ");");
    template.update(site);
  }
  
  
  public Long newSong(Song song) throws Exception {
    Session session = getSession(); 
    HibernateTemplate template =  getHibernateTemplate();
    
    // Get the last id value for the insert
    String queryString = "select max(s.id) from Song s";
    Query query = session.createQuery(queryString);
    Long lastId = (Long) query.uniqueResult();
    if (lastId == null) {
      lastId = new Long(0);
    }
    song.setId(++lastId);
    System.out.println("template.save(" + song.getId() + ");");
    template.save(song);
    return lastId; 
  }
  
  
  public Long update(Site site) throws Exception {
    Session session = getSession(); 
    HibernateTemplate template =  getHibernateTemplate();
    System.out.println("template.update(" + site.getId() + ");");
    template.update(site);
    return site.getId(); 
  }
  
  public void updatePage(Page page) throws Exception {
    HibernateTemplate template =  getHibernateTemplate();
    System.out.println("template.update(" + page.getId() + ");");
    template.update(page);
  }
  
  public void updatePages(List pages) throws Exception {
    HibernateTemplate template =  getHibernateTemplate();
    
    for (Iterator i = pages.iterator(); i.hasNext();) {
      Page page = (Page) i.next();
      System.out.println("template.update(" + page.getId() + ");");
      template.update(page);
    }
  }
  
  public List find() throws Exception {
    HibernateTemplate template =  getHibernateTemplate();
    List list = template.find("from Site s where s.active = true");
    System.out.println("Returning sites: " + list.size());
    return list;
  }
  
  public Site find(Long id) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Site.class);
    crit.add(Expression.eq("id", id));    
    Site site = (Site)crit.uniqueResult();
    return site;
  }
  
  public Site find(String label) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Site.class);
    crit.add(Expression.eq("pathLabel", label));    
    Site site = (Site)crit.uniqueResult();
    return site;
  }
  
  public List getPages(Long siteId) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Page.class);
    crit.add(Expression.eq("siteId", siteId));    
    List list = crit.list();
    System.out.println("Returning pages: " + list.size());
    return list;
  }
  
  public List getReviews(Long siteId) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Review.class);
    crit.add(Expression.eq("siteId", siteId));    
    List list = crit.list();
    System.out.println("Returning reviews: " + list.size());
    return list;
  }
  
  public List getNewReviews(Long siteId) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Review.class);
    crit.add(Expression.eq("siteId", siteId));    
    crit.add(Expression.eq("approved", false));    
    List list = crit.list();
    System.out.println("Returning new reviews: " + list.size());
    return list;
  }
  
  
  public Song getSong(Long id) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Song.class);
    crit.add(Expression.eq("id", id));    
    Song song = (Song)crit.uniqueResult();
    return song;
  }  
  
  public Review getReview(Long id) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Review.class);
    crit.add(Expression.eq("id", id));    
    Review review = (Review)crit.uniqueResult();
    return review;
  } 
  
  
  public Playlist getPlaylist(Long id) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Playlist.class);
    crit.add(Expression.eq("id", id));    
    Playlist playlist = (Playlist)crit.uniqueResult();
    return playlist;
  }  
  
  public Playlist getPlaylistFromNewSite(Long siteId) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Playlist.class);
    crit.add(Expression.eq("siteId", siteId));    
    Playlist playlist = (Playlist)crit.uniqueResult();
    return playlist;
  } 
  
  
  
  public Video getVideo(Long id) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Video.class);
    crit.add(Expression.eq("id", id));    
    Video video = (Video)crit.uniqueResult();
    return video;
  } 
  
  
  public List approveNewReviews(Long siteId, List rejectedReviewIds) throws Exception {
    
    Session session = getSession(); 
    HibernateTemplate template =  getHibernateTemplate();
   
    // delete all new reviews from this site that are still not approved
    for (Iterator i = rejectedReviewIds.iterator(); i.hasNext();) {
      Long reviewId = new Long(((Integer)i.next()).intValue());
      Review review = getReview(reviewId);
      System.out.println("template.delete(" + review.getId() + ");");
      template.delete(review);
    }
    
    // update remaining new reviews from this site to approved status
    Criteria crit = session.createCriteria(Review.class);
    crit.add(Expression.eq("siteId", siteId));    
    List list = crit.list();
    for (Iterator i = list.iterator(); i.hasNext();) {
      Review review = (Review) i.next();
      review.setApproved(true);
      System.out.println("template.update(" + review.getId() + ");");
      template.update(review);
    }
    
    // return remaining reviews from site id. 
    return getSiteReviews(siteId);
  }
  
  public List getSongsInPlaylist(Long playlistId) throws Exception {
    List songIds = new ArrayList();
    List list = new ArrayList();
    
    Playlist playlist = getPlaylist(playlistId);
    
    if (playlist.getSongIds() != null) {
      String[] songIdStrings = playlist.getSongIds().split(",");
    
      for (int i = 0 ; i < songIdStrings.length ; i++) {
        if (songIdStrings[i] != null && !songIdStrings[i].equals("")) {
          Long id = new Long(songIdStrings[i]);
          songIds.add(id);
        }
      }
      // Long[] songIds = new Long[] {1L,2L,5L};  // legal

      Session session = getSession(); 
      Criteria crit = session.createCriteria(Song.class);
      crit.add(Expression.in("id", songIds));    
      Order ord  = Order.asc("path");
      crit.addOrder(ord);
      list = crit.list();
    } 
    return list;
  }
  
  
  public List getSitePlaylists(Long siteId) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Playlist.class);
    crit.add(Expression.eq("siteId", siteId));    
    List list = crit.list();
    System.out.println("Returning playlists: " + list.size());
    return list;
  }
  
  
  public List getSiteReviews(Long siteId) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Review.class);
    crit.add(Expression.eq("siteId", siteId));    
    crit.add(Expression.eq("approved", true));    
    List list = crit.list();
    System.out.println("Returning reviews: " + list.size());
    return list;
  }
  
  
  public List getSiteVideos(Long siteId) throws Exception {
    Session session = getSession(); 
    Criteria crit = session.createCriteria(Video.class);
    crit.add(Expression.eq("siteId", siteId));    
    List list = crit.list();
    System.out.println("Returning videos: " + list.size());
    return list;
  }
  
  
  public void addSongToPlaylist(Song song, Long playlistId) throws Exception {
    
    Playlist playlist = getPlaylist(playlistId);
    String[] songIdStrings;
    
    if (playlist.getSongIds() != null) {
      songIdStrings = playlist.getSongIds().split(",");
    
      for (int i = 0 ; i < songIdStrings.length ; i++) {
        Long id = new Long(songIdStrings[i]);
        if(id == song.getId()) {
          return; 
        }
      }
    } 
    else {
      songIdStrings = new String[0];
    }
  
    String[] temp = new String[songIdStrings.length+1];
    System.arraycopy(songIdStrings,0,temp,0,songIdStrings.length);
    temp[songIdStrings.length] = song.getId().toString();
    songIdStrings = temp;
    playlist.setSongIds(StringUtils.join(songIdStrings, ","));
      
    HibernateTemplate template =  getHibernateTemplate();
    template.update(playlist);
  }
  
}
